﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Linq;

namespace StockCrawlerLib.DAL
{
    public class HistoryRepository
    {
        private StockDataClassesDataContext dc = new StockDataClassesDataContext();

        /// <summary>
        /// Kiểm tra tồn tại ngày giao dịch không?
        /// </summary>
        /// <param name="id_stock"></param>
        /// <param name="ngay_giao_dich"></param>
        /// <returns></returns>
        public bool IsExist(int id_stock, DateTime ngay_giao_dich)
        {
            bool result = false;

            int count = SelectRecord(id_stock, ngay_giao_dich)
                .ToList()
                .Count();
            if (count > 0)
            {
                result = true;
            }
            return result;
        }

        public IQueryable<history> Select(int id_stock)
        {
            // obj = dc.histories.AsQueryable().Select(p=>p.id_stock == id_stock && p.ngay_giao_dich == ngay_giao_dich)
            var query = from p in dc.histories
                        where p.id_stock == id_stock
                        orderby p.ngay_giao_dich
                        select p;
            return query;
        }

        public IQueryable Select2(int id_stock)
        {
            // obj = dc.histories.AsQueryable().Select(p=>p.id_stock == id_stock && p.ngay_giao_dich == ngay_giao_dich)
            var query = (from p in dc.histories
                        where p.id_stock == id_stock
                        orderby p.ngay_giao_dich
                        select new
                        {
                            p.stock.code,
                            p.stock.stockexchange.stockexchange_name,
                            p.ngay_giao_dich,
                            p.gia_tham_chieu,
                            p.gia_tran,
                            p.gia_san,
                            p.gia_dong_cua,
                            p.thay_doi,
                            p.thay_doi_percent,
                            p.gia_mo_cua,
                            p.gia_cao_nhat,
                            p.gia_thap_nhat,
                            p.klgd_khop_lenh,
                            p.gtgd_khop_lenh,
                            p.so_lenh_mua,
                            p.kl_dat_mua,
                            p.so_lenh_ban,
                            p.kl_dat_ban,
                            p.klgd_thoa_thuan,
                            p.klsh_nn,
                            p.klsh_nn_percent,
                            p.klsh_nn_con_lai,
                            p.klgd_nn_mua,
                            p.klgd_nn_ban,
                            p.klgd_nn_thoa_thuan_mua,
                            p.klgd_nn_thoa_thuan_ban
                        }).OrderByDescending(p=>p.ngay_giao_dich).Take(30);
            return query;
        }

        public IQueryable<history> SelectRecord(int id_stock, DateTime ngay_giao_dich)
        {
            // obj = dc.histories.AsQueryable().Select(p=>p.id_stock == id_stock && p.ngay_giao_dich == ngay_giao_dich)
            var query = from p in dc.histories
                        where p.id_stock == id_stock
                        && p.ngay_giao_dich.Day == ngay_giao_dich.Day
                        && p.ngay_giao_dich.Month == ngay_giao_dich.Month
                        && p.ngay_giao_dich.Year == ngay_giao_dich.Year                        
                        select p;
            return query;
        }

        public IQueryable SelectByNgayGiaoDich(DateTime ngay_giao_dich)
        {
            DateTime dtMax = dc.histories.Max(u1 => u1.ngay_giao_dich);;
            if (dtMax < ngay_giao_dich)
                ngay_giao_dich = dtMax;

            var query = from p in dc.histories
                        where p.ngay_giao_dich.Day == ngay_giao_dich.Day
                        && p.ngay_giao_dich.Month == ngay_giao_dich.Month
                        && p.ngay_giao_dich.Year == ngay_giao_dich.Year
                        select new
                        {
                            p.id_stock,
                            p.stock.code,
                            exchange = p.stock.stockexchange.stockexchange_name,
                            p.ngay_giao_dich,
                            p.gia_dong_cua,
                            p.thay_doi,
                            p.gia_tham_chieu,
                            p.gia_mo_cua,
                            p.gia_cao_nhat,
                            p.gia_thap_nhat,
                            p.klgd_khop_lenh,
                            p.gtgd_khop_lenh,
                            p.so_lenh_mua,
                            p.kl_dat_mua,
                            p.so_lenh_ban,
                            p.kl_dat_ban
                        };
            return query;
        }

        public IQueryable Select_MaxNgayGiaoDich()
        {
            var query = from p in dc.histories
                        where p.ngay_giao_dich == (dc.histories.Max(u1 => u1.ngay_giao_dich))
                        select new
                        {
                            p.stock.code,
                            exchange = p.stock.stockexchange.stockexchange_name,
                            p.ngay_giao_dich,
                            p.gia_dong_cua,
                            p.thay_doi,
                            p.gia_tham_chieu,
                            p.gia_mo_cua,
                            p.gia_cao_nhat,
                            p.gia_thap_nhat,
                            p.klgd_khop_lenh,
                            p.gtgd_khop_lenh,
                            p.so_lenh_mua,
                            p.kl_dat_mua,
                            p.so_lenh_ban,
                            p.kl_dat_ban
                        };
            return query;
        }

        private IQueryable GetFilterResult(string query)
        {
            var result = from p in dc.ExecuteQuery<stock>(query)
                         where (p.id_stockexchange == 1 || p.id_stockexchange == 2 || p.id_stockexchange == 9)
                        && p.status == true
                         select new
                         {
                             p.id_stock,
                             p.code,
                             p.stockexchange.stockexchange_name,
                             p.name,
                             p.pe,
                             p.eps,
                             p.bv,
                             p.klcp_luuhanh,
                             p.klcp_niemyet,
                             p.vcsh,
                             p.isRemark
                         };

            return result.AsQueryable();
        }


        public IQueryable SelectCEFLFilter(bool isCE, int numDaysAfter, int numDaysBefore, string changedRange, string volumeRange)
        {
            string query = "select * from stock h1 where";
            IQueryable<DateTime> queryNgayGDBefore = (from p in dc.histories
                                                      where p.stock.status == true
                                                      orderby p.ngay_giao_dich
                                                      select p.ngay_giao_dich).Distinct().OrderByDescending(p => p.Date).Take(numDaysBefore);

            if (numDaysAfter > 0)
            {
                List<DateTime> ngayGDBeforeList = queryNgayGDBefore.ToList<DateTime>();
                IQueryable<DateTime> queryNgayGDAfter = (from p in dc.histories
                                                         where p.stock.status == true && p.ngay_giao_dich < ngayGDBeforeList[ngayGDBeforeList.Count - 1]
                                                         orderby p.ngay_giao_dich
                                                         select p.ngay_giao_dich).Distinct().OrderByDescending(p => p.Date).Take(numDaysAfter);
                //Những cổ phiếu trần/sàn sau khi đã sàn/trần liên tục
                string changeStatusBefore = string.Format("h2.gia_dong_cua = {0}", isCE ? "h2.gia_tran" : "h2.gia_san");
                string changeStatusAfter = string.Format("h2.gia_dong_cua = {0}", isCE ? "h2.gia_san" : "h2.gia_tran");

                foreach (DateTime d in queryNgayGDBefore)
                {
                    query += string.Format(" id_stock in (select top 1 id_stock from history h2 where ngay_giao_dich = '{0}' and {1} and {2} and {3} and h1.id_stock = h2.id_stock order by ngay_giao_dich desc)", d.Date, changeStatusBefore, changedRange, volumeRange);
                    query += " and ";
                }

                foreach (DateTime d in queryNgayGDAfter)
                {
                    query += string.Format(" id_stock in (select top 1 id_stock from history h2 where ngay_giao_dich = '{0}' and {1} and {2} and {3} and h1.id_stock = h2.id_stock order by ngay_giao_dich desc)", d.Date, changeStatusAfter, changedRange, volumeRange);
                    query += " and ";
                }
            }
            else
            {
                string changeStatusBefore = string.Format("h2.gia_dong_cua = {0}", isCE ? "h2.gia_tran" : "h2.gia_san");
                foreach (DateTime d in queryNgayGDBefore)
                {
                    query += string.Format(" id_stock in (select top 1 id_stock from history h2 where ngay_giao_dich = '{0}' and {1} and {2} and {3} and h1.id_stock = h2.id_stock order by ngay_giao_dich desc)", d.Date, changeStatusBefore, changedRange, volumeRange);
                    query += " and ";
                }
            }
            query += "end";
            query = query.Replace(" and end", "");
         
            return GetFilterResult(query);
        }

        public IQueryable SelectByFilter2(bool isIncrease, int numDaysAfter, bool isIncludeNoneChangeAfter, int numDaysBefore, bool isIncludeNoneChangeBefore, string changedRange, string volumeRange)
        {
            string query = "select * from stock h1 where";
            IQueryable<DateTime> queryNgayGDBefore = (  from p in dc.histories
                                                        where p.stock.status == true
                                                        orderby p.ngay_giao_dich
                                                        select p.ngay_giao_dich).Distinct().OrderByDescending(p => p.Date).Take(numDaysBefore);

            if (numDaysAfter > 0)
            {
                List<DateTime> ngayGDBeforeList = queryNgayGDBefore.ToList<DateTime>();
                IQueryable<DateTime> queryNgayGDAfter = (from p in dc.histories 
                                     where p.stock.status == true && p.ngay_giao_dich < ngayGDBeforeList[ngayGDBeforeList.Count-1]
                                     orderby p.ngay_giao_dich
                                     select p.ngay_giao_dich).Distinct().OrderByDescending(p => p.Date).Take(numDaysAfter);
                
                //Những cổ phiếu tăng/giảm sau khi đã giảm/tăng liên tục
                string changeStatusAfter = string.Format("thay_doi {0}", isIncrease ? "< 0" : "> 0");
                if (isIncludeNoneChangeAfter)
                    changeStatusAfter = string.Format("thay_doi {0}", isIncrease ? "<= 0" : ">= 0");
                
                //Những cổ phiếu giá giảm/tăng liên tục
                string changeStatusBefore = string.Format("thay_doi {0}", isIncrease ? "> 0" : "< 0");
                if(isIncludeNoneChangeBefore)
                    changeStatusBefore = string.Format("thay_doi {0}", isIncrease ? ">= 0" : "<= 0");
                
                foreach (DateTime d in queryNgayGDBefore)
                {
                    query += string.Format(" id_stock in (select top 1 id_stock from history h2 where ngay_giao_dich = '{0}' and {1} and {2} and {3} and h1.id_stock = h2.id_stock order by ngay_giao_dich desc)", d.Date, changeStatusBefore, changedRange, volumeRange);
                    query += " and ";
                }

                foreach (DateTime d in queryNgayGDAfter)
                {
                    query += string.Format(" id_stock in (select top 1 id_stock from history h2 where ngay_giao_dich = '{0}' and {1} and {2} and {3} and h1.id_stock = h2.id_stock order by ngay_giao_dich desc)", d.Date, changeStatusAfter, changedRange, volumeRange);
                    query += " and ";
                }
            }
            else
            {
                //Những cổ phiếu giá giảm/tăng liên tục
                string changeStatusBefore = string.Format("thay_doi {0}", isIncrease ? "> 0" : "< 0");
                if (isIncludeNoneChangeBefore)
                    changeStatusBefore = string.Format("thay_doi {0}", isIncrease ? ">= 0" : "<= 0");
                
                foreach (DateTime d in queryNgayGDBefore)
                {
                    query += string.Format(" id_stock in (select top 1 id_stock from history h2 where ngay_giao_dich = '{0}' and {1} and {2} and {3} and h1.id_stock = h2.id_stock order by ngay_giao_dich desc)", d.Date, changeStatusBefore, changedRange, volumeRange);
                    query += " and ";
                }
                
            }
            query += "end";
            query = query.Replace(" and end", "");

            return GetFilterResult(query);
        }

        public void Insert(history obj)
        {
            dc.histories.InsertOnSubmit(obj);
            dc.SubmitChanges();
        }

        public void Update(history obj)
        {
            dc.Refresh(RefreshMode.KeepChanges, obj);
            dc.SubmitChanges(ConflictMode.ContinueOnConflict);
        }

        public void Delete(history obj)
        {
            dc.histories.DeleteOnSubmit(obj);
            dc.SubmitChanges();
        }

    }
}
